BEGIN;

ALTER TABLE dance_class ADD COLUMN level VARCHAR(16) NOT NULL DEFAULT 'BEGINNER';

CREATE TABLE email (
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	sid VARCHAR(32) NOT NULL,
	sender VARCHAR(64) NOT NULL,
	subject VARCHAR(256) NOT NULL,
	content TEXT NOT NULL,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE email ADD INDEX email_sid_idx (sid);

CREATE TABLE room (
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	sid VARCHAR(32) NOT NULL,
	name VARCHAR(128) NOT NULL,
	UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE room ADD INDEX room_sid_idx (sid);

ALTER TABLE dance_class ADD COLUMN room_id INT(11);
ALTER TABLE dance_class ADD FOREIGN KEY (room_id) REFERENCES room(id) ON DELETE SET NULL;

COMMIT;